#!/usr/bin/env python

from config import *

import sys
import logging
from errorCollator import *

LOGFILENAME = __file__ + ".log"
logging.basicConfig(format='%(asctime)s %(levelname)s: %(message)s', 
      filename = LOGFILENAME, level=logging.INFO)

hiccups = ErrorCollator()

from pdbWrapper import PDBWS
from ZSI import FaultException
from xmlMapping.dbConnect import *


class UserWatcher:

  def __init__(self):
    self._log = logging.getLogger('%s.%s' % (__name__, self.__class__.__name__))

  @staticmethod
  def oracleEscape(self,strn):
    if not strn: return ''
    if strn=='none': return ''
    if strn=='None': return ''
    out = []
    for char in strn:
      if char =="'":
        out.append("'")
        out.append(char)
      else:
        out.append(char)
    ret = "".join(out)
    return ret

  def cleanup(self):
    if self.pdbcnx:
      self.pdbcnx.logout()
      self._log.info("Closed CSAF ProposalDB connection")
    if self.dbcnx:
      self.dbcnx.finish() 
      self._log.info("Closed ICAT DB connection")

  def main(self):
    # ping proposaldb server, get all new users since given date/time,
    self._log.info("Connecting CSAF ProposalDB")
    self.pdbcnx = PDBWS(PDBWSURL,PDBWSUSER,PDBWSPASS)
    try:
        date = (2003,1,1, 0,0,1, 0,0,0)  # ZSI needs 9 elem tuple for dates
        newUsers = self.pdbcnx.getAllScientistsSince(date)
        scientists = newUsers['scientists']
        for scientist in scientists:
          self._log.debug("   %s %s %s %s ",
             scientist['first_Names'],scientist['last_Name'],
             scientist['login_ID'],scientist['email'] )
        self._log.info(" %d new scientists since %s", len(scientists) , date)
    except FaultException, e:
        self._log.critical("-------------- %s ",e)
        hiccups.add(Error(__name__,__file__,e) )
        return 1
    
    
    #populate remote ICAT FACILITY_USER table with these users
    self._log.info("Connecting: ICAT Oracle Database")
    bypass=False
    try:
      self.dbcnx = DBConnection(bypass)
      self.dbcnx.cxInit(ICATDBHOST, ICATDBPORT, ICATDBNAME, ICATDBUSER, ICATDBPASS)
      self.dbcnx.connect()     # establish tcp communication
    except Exception, e:
      self._log.critical(e)
      self._log.critical( "You may need to port forward over SSH to tunnel through firewall")
      self._log.critical( "e.g.   ssh dataminx -L 1521:localhost:1521")
      hiccups.add(Error(__name__,__file__,e) )
      hiccups.add(Error(__name__,__file__,
        "Potentialy the database server " + ICATDBHOST + " is down, or we have misconfigured connection settings (in config.py).") )
      return 2
    self._log.info("Remote connections established" )
    
    addingOnlyOneNewUser=0
    
    for scientist in scientists:
      try:
        DBSCHEMA="ICAT"
        res=self.dbcnx.query("""SELECT * FROM FACILITY_USER WHERE FACILITY_USER_ID='%(name)s' OR FACILITY_USER_ID='%(email)s'""" % {'DBSCHEMA' : DBSCHEMA,'name': scientist['login_ID'], 'email': scientist['email'] } )
      except Exception, msg:
        #import traceback
        #traceback.print_exc(file=sys.stdout)
        self._log.critical("Select from facility_user failed\n%s" % msg)
        hiccups.add(Error(__name__,__file__,msg) )
        return 3
      if not res or res.ntuples > 1:
        self._log.debug("uhoh many results")
        self._log.debug("   %s %s %s %s ",
             scientist['first_Names'],scientist['last_Name'],
             scientist['login_ID'],scientist['email'] )
        continue
      elif res.ntuples ==1:
        continue
        for row in res:
          self._log.debug(row)
          self._log.debug("   %s %s %s %s ",
             scientist['first_Names'],scientist['last_Name'],
             scientist['login_ID'],scientist['email'] )
      else:   # res.ntuples <=0
        self._log.info("no ICAT entry exists for:")
        self._log.info("   %s %s %s %s ",
             scientist['first_Names'],scientist['last_Name'],
             scientist['login_ID'],scientist['email'] )
    
      addingOnlyOneNewUser += 1 
    
    #  if addingOnlyOneNewUser > 1: 
    #       print "skipping ICAT add of" , scientist['login_ID']
    #       continue
    #  print scientist
    
      first = scientist['first_Names']
      parts = first.split(" ")
      init = []
      middle = ''
      for part in parts:
        init.append(part[0].upper())
        init.append(".")
      initial = "".join(init)
      if len(first)>1:
        middle = " ".join(parts[1:])
      first = parts[0]
      try:
        query= """INSERT INTO FACILITY_USER (FACILITY_USER_ID, FEDERAL_ID, 
           TITLE, INITIALS, FIRST_NAME, MIDDLE_NAME, LAST_NAME, 
           MOD_TIME, MOD_ID, CREATE_TIME, CREATE_ID, 
            FACILITY_ACQUIRED, DELETED) VALUES 
         ('%(name)s', '%(name)s', '%(title)s', '%(initials)s', '%(first)s', 
          '%(middle)s', '%(last)s',sysdate,'damian',sysdate,'damian', 'Y','N')""" % \
         {'DBSCHEMA' : DBSCHEMA, 
           'name': oracleEscape(scientist['login_ID']), 
           'email': oracleEscape(scientist['email']), 
           'title': oracleEscape(scientist['title']), 
           'initials':oracleEscape(initial),  
           'first': oracleEscape(first), 
           'middle': oracleEscape(middle),
           'last': oracleEscape(scientist['last_Name']), 
         }  # )
        self._log.debug(query)
    ########
        res=self.dbcnx.query(query)
        self._log.info("Added new ICAT user %s", scientist['login_ID'] )
    ########
      except Exception, msg:
        self._log.critical("Failed to inject new  facility_user: %s\n" %  scientist['email'] )
        self._log.critical(msg)
        hiccups.add(Error(__name__,__file__,msg) )
        return 4
         
    self.dbcnx.query("commit")
    self._log.info(" %d new ICAT additions", addingOnlyOneNewUser )



if __name__ == '__main__':
    watcher = UserWatcher()
    try:
      rv = watcher.main()
    except Exception, msg:
      watcher._log.critical(msg)
    watcher.cleanup()
    import socket
    thisHost = socket.gethostbyaddr(socket.gethostname())
    fqdn = thisHost[1][0]
    if hiccups.hasErrors():
       hiccups.dump()  # stdout - should go to  cron post run email  
       hiccups.emailAlert("userWatcher@"+fqdn, EMAIL_ALERT_LIST, EMAIL_SMTP_HOST,
                    "New ICAT user addition errors " + __file__ ) 


